package cc.zy.base.hr.util;


import cc.zy.base.businesses.entity.ErrorLog;
import cc.zy.base.hr.entity.*;
import cc.zy.base.common.entity.FebsResponse;
import com.wuwenze.poi.annotation.ExcelField;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import org.springframework.boot.system.ApplicationHome;
import org.springframework.web.multipart.MultipartFile;

import java.io.*;
import java.lang.reflect.Field;
import java.util.*;

import static org.apache.poi.ss.usermodel.CellStyle.*;
import static org.apache.poi.ss.usermodel.FillPatternType.SOLID_FOREGROUND;

/**
 * 读取excel的工具类
 *
 * @author LiuZhuang
 * @date 2022/03/08 19:04:53
 */
@Slf4j
public class ExcelUtil {
     /**
         * 方法描述：读取解析上传的薪资总表
         *
         * @return
         * @author LiuZhuang
         * @date 2022/03/09
         *
         */
    public static FebsResponse readData(MultipartFile multipartFile) throws NoSuchFieldException, IllegalAccessException, IOException {
        //将表中数据读到此集合中
        List<Emolument> hrEmolumentExcels = new ArrayList<>();
        //记录错误日志
        List<ErrorLog> errorLogs = new ArrayList<>();
        //将表头信息和对应列数放入map集合中
        Map<String, Object> titleMap = new HashMap<>();
        InputStream inputStream = multipartFile.getInputStream();
        log.debug(multipartFile.getName());
        //获取一个UUID作为当次操作日志记录的唯一标记
        String unique = UUID.randomUUID().toString();

        log.debug(String.format("unique = %s", unique));
        // 工作表
        Workbook workbook = null;
        try {
            workbook = WorkbookFactory.create(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (InvalidFormatException e) {
            e.printStackTrace();
        }
        // 表个数。
        int numberOfSheets = workbook.getNumberOfSheets();
        // 遍历表。
        for (int i1 = 0; i1 < numberOfSheets; i1++) {

            Sheet sheet = workbook.getSheetAt(i1);
            // Excel第一行。
            Row temp = sheet.getRow(0);
            if (temp == null) {
                continue;
            }
            // 读数据。
            //4、循环读取表格数据
//            for (Row row : sheet)
            for (int i2 = 0; i2 < sheet.getPhysicalNumberOfRows(); i2++) {
                Row row=sheet.getRow(i2);
                int columNos = row.getLastCellNum();// 表头总共的列数
                //目前读取的行数
                int rowNum = row.getRowNum();
                //首行（即表头）不读取
                if (rowNum<2){
                    continue;
                }
                if (rowNum == 2) {
                    for (int i = 0; i <= columNos; i++) {
                        Cell cell = row.getCell(i);
                        if(cell != null){
                            cell.setCellType(CellType.STRING);
                            titleMap.put(cell.getStringCellValue(),i);
                        }
                    }
                    continue;
                }
                if (rowNum == 3){
                    for (int i = 4; i <= columNos; i++) {
                        Cell cell = row.getCell(i);
                        if(cell != null){
                            cell.setCellType(CellType.STRING);
                            titleMap.put(cell.getStringCellValue(),i);
                        }
                    }
                    continue;
                }
                for (Cell cell : row) {
                    cell.setCellType(CellType.STRING);
                }
                Emolument hrEmolumentExcel = new Emolument();
//                获取class对象
                Class c=hrEmolumentExcel.getClass();
//                获取所有的字段名称
                Field[] fields = c.getDeclaredFields();
                //读取当前行中单元格数据，索引从0开始

                /**
                 * 获取表格中的员工基本信息
                 * 身份证号
                 * 银行
                 * 员工姓名
                 */
                Integer IDCardIndex=(Integer) titleMap.get("身份证号");
                log.info("身份证号 "+IDCardIndex);
                String IDCard=null;
                Cell IDCardCell = row.getCell(IDCardIndex);
                log.info("身份证号 "+IDCardCell);
                if (IDCardCell == null || "".equals(IDCardCell)){
                    ErrorLog errorLog = new ErrorLog();
                    errorLog.setErrorRow(rowNum+1);
                    errorLog.setErrorColumn(IDCardIndex+1);
                    errorLog.setUnique(unique);
                    errorLog.setCreateTime(new Date(System.currentTimeMillis()));
                    errorLog.setReason("身份证信息为空！");
                    errorLogs.add(errorLog);
                }else {
                    IDCard = IDCardCell.getStringCellValue();
                    hrEmolumentExcel.setIdCard(IDCard);
                }


                Integer staffNameIndex=(Integer) titleMap.get("姓名");
                Cell staffNameCell = row.getCell(staffNameIndex);
                String staffName=null;
                if (staffNameCell==null||"".equals(staffNameCell)){
                    ErrorLog errorLog = new ErrorLog();
                    errorLog.setErrorRow(rowNum+1);
                    errorLog.setErrorColumn(staffNameIndex+1);
                    errorLog.setUnique(unique);
                    errorLog.setCreateTime(new Date(System.currentTimeMillis()));
                    errorLog.setReason("姓名为空！");
                    errorLogs.add(errorLog);
                }else {
                    staffName = staffNameCell.getStringCellValue();
                    hrEmolumentExcel.setName(staffName);
                }

                Integer bankIndex=(Integer) titleMap.get("银行");
                Cell bankCell = row.getCell(bankIndex);
                String bank=null;
                if (bankCell==null||"".equals(bankCell)){
                    ErrorLog errorLog = new ErrorLog();
                    errorLog.setErrorRow(rowNum+1);
                    errorLog.setErrorColumn(bankIndex+1);
                    errorLog.setUnique(unique);
                    errorLog.setCreateTime(new Date(System.currentTimeMillis()));
                    errorLog.setReason("银行为空！");
                    errorLogs.add(errorLog);
                }else {
                    bank = bankCell.getStringCellValue();
                    hrEmolumentExcel.setBank(bank);
                }

                //这里需要加入判断员工身份证号和姓名是否对应
                //根据身份证号码查询员工姓名进行比较，不符合则在errorlog中加入错误信息




                /**
                 * 获取表格中的员工薪酬基本信息
                 * 此处不需要验证直接取出放在list中
                 * 由于字段过多采用反射机制获取
                 * 这段代码在读取不需要验证的表格内容时候可以简单修改后重用
                 */
                for (Field f : fields) {
                    // 判断字段注解是否存在
                    boolean annotationPresent2 = f.isAnnotationPresent(ExcelField.class);
                    if (annotationPresent2) {
                        ExcelField name = f.getAnnotation(ExcelField.class);
                        // 获取注解值
                        String nameStr = name.value();
                        log.info(nameStr);
                        if ("身份证号".equals(nameStr)|"姓名".equals(nameStr)|"银行".equals(nameStr)){
                            continue;
                        }
                        Integer index=(Integer) titleMap.get(nameStr);
                        Cell cell=row.getCell(index);
                        if ("".equals(cell)||cell==null){
                            continue;
                        }else{
                            String fieldValue=cell.getStringCellValue();
                            try {
                                f.setAccessible(true);
                                f.set(hrEmolumentExcel,fieldValue);
                            } catch (IllegalAccessException e) {
                                e.printStackTrace();
                            }
                        }
                    }
                }
                //设置行号
                hrEmolumentExcel.setRow(rowNum+1);
                hrEmolumentExcels.add(hrEmolumentExcel);
            }

            //5、关闭流
            try {
                workbook.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        //判断是否有错误
        if (errorLogs==null||errorLogs.isEmpty()){
            return new FebsResponse().message("200").data(hrEmolumentExcels);
        }else {
            return new FebsResponse().message("400").data(errorLogs);
        }
    }

     /**
         * 方法描述：生成招行对应的Excel表格
         *
         * @return 生成Excel文件的保存路径
         * @param TotalName 总表名称
         * @author LiuZhuang
         * @date 2022/03/10
         *
         */
     public static String creatZhaoExcel(String TotalName, List<Emolument> emoluments, ApplicationHome applicationHome) throws IOException {
         String path=applicationHome.getDir().getParentFile().getParentFile().getAbsolutePath()+"\\salary\\submeter\\"+TotalName.substring(0,8);
         log.info(TotalName.substring(0,8));
         List<ZhaoHang> bankExcel=TypeUtil.getZhaoHangExcel(emoluments);
         String lastBankName="（招行）";
         File file = new File(path,TotalName.substring(0,11)+lastBankName+".xlsx");
//          如果文件夹不存在就创建文件夹
         File parentFile = file.getParentFile();
         if (!parentFile.exists()){
             parentFile.mkdir();
         }
         log.info(file.getPath());
         OutputStream os=new FileOutputStream(file);
         Workbook book  = new XSSFWorkbook();
         if(file.exists()) {
             file.delete();
         }
//         if(!file.exists()) {
//             file.createNewFile();
//         }
//         此处生成表头根据银行不同需要自定义生成不同的表头
         Sheet sheet = book.createSheet("招行");
         String[] title = {"账户名称(*)", "账号(*)", "金额(*)","摘要","开户银行","开户省(-)","开户市(-)","账户类型"};
         Row titleRow = sheet.createRow(0);

         for(int i = 0; i < title.length; i++) {
//             获取第一列
             Cell cell = titleRow.createCell(i );
//             写入第一列的内容
             cell.setCellValue(title[i]);
         }
         int lastRowNum = sheet.getLastRowNum();
         for(ZhaoHang zhaohang : bankExcel) {
             Row currentRow = sheet.createRow(lastRowNum + 1);
             currentRow.createCell(0).setCellValue(zhaohang.getName());
             currentRow.createCell(1).setCellValue(zhaohang.getAccount());
             currentRow.createCell(2).setCellValue(zhaohang.getAmount());
             currentRow.createCell(3).setCellValue(zhaohang.getAbstracts());
             currentRow.createCell(4).setCellValue(zhaohang.getBank());
             currentRow.createCell(5).setCellValue(zhaohang.getProvince());
             currentRow.createCell(6).setCellValue(zhaohang.getCity());
             currentRow.createCell(7).setCellValue(zhaohang.getType());
         }
         book.write(os);
         book.close();
//         返回了一个null，后续返回存储的地址以便后端获取传递给前端
         return null;
     }


    /**
     * 方法描述：生成中行对应的Excel表格
     *
     * @return 生成Excel文件的保存路径
     * @param TotalName 总表名称
     * @author LiuZhuang
     * @date 2022/03/10
     *
     */
    public static String creatZhongExcel(String TotalName, List<Emolument> emoluments, ApplicationHome applicationHome) throws IOException {
        String path=applicationHome.getDir().getParentFile().getParentFile().getAbsolutePath()+"\\salary\\submeter\\"+TotalName.substring(0,8);
        List<ZhongHang> bankExcel=TypeUtil.getBOCExcel(emoluments);
        String lastBankName="（中行）";
        File file = new File(path,TotalName.substring(0,11)+lastBankName+".xlsx");
//          如果文件夹不存在就创建文件夹
        File parentFile = file.getParentFile();
        if (!parentFile.exists()){
            parentFile.mkdir();
        }
        OutputStream os=new FileOutputStream(file);
        Workbook book  = new XSSFWorkbook();
        if(file.exists()) {
            file.delete();
        }
//         if(!file.exists()) {
//             file.createNewFile();
//         }


//         此处生成表头根据银行不同需要自定义生成不同的表头
        Sheet sheet = book.createSheet("中国银行模板");
        String[] title = {"业务类型：", "C1-人民币/外币行内代付", "转出账号：","6217000000000000","币种：","CNY-人民币","业务摘要：","EV-工资",""};
        Row titleRow = sheet.createRow(0);
        for(int i = 0; i < title.length; i++) {
//             获取第一列
            Cell cell = titleRow.createCell(i );
//             写入第一列的内容
            cell.setCellValue(title[i]);
        }
//        中国银行第二列为空
        Row titleRow1 = sheet.createRow(1);
        Cell cellNull=titleRow1.createCell(1);
        cellNull.setCellValue("");
//      生成第三列表头
        String[] title1 = {"序号", "转入账号", "转入名称：","金额","转入行省行","证件类型","证件号码","备注","错误标识"};
        Row titleRow2 = sheet.createRow(2);
        for(int i = 0; i < 9; i++) {
//             获取第一列
            Cell cell = titleRow2.createCell(i);
            cell.setCellValue(title1[i]);
        }
        int lastRowNum = sheet.getLastRowNum();
        int row=1;
        for(ZhongHang zhongHang : bankExcel) {
            Row currentRow = sheet.createRow(lastRowNum + 1);
            currentRow.createCell(0).setCellValue(row++);
            currentRow.createCell(1).setCellValue(zhongHang.getAccount());
            currentRow.createCell(2).setCellValue(zhongHang.getName());
            currentRow.createCell(3).setCellValue(zhongHang.getAmount());
            currentRow.createCell(4).setCellValue(zhongHang.getBank());
            currentRow.createCell(5).setCellValue(zhongHang.getType());
            currentRow.createCell(6).setCellValue(zhongHang.getIdNumber());
            currentRow.createCell(7).setCellValue(zhongHang.getRemark());
            currentRow.createCell(8).setCellValue(zhongHang.getErrorSign());
        }
        book.write(os);
        book.close();
//         返回了一个null，后续返回存储的地址以便后端获取传递给前端
        return null;
    }


      /**
         * 方法描述：生成重庆银行Excel
         *
         * @return TotalName 总表名称
         * @author LiuZhuang
         * @date 2022/03/10
         *
         */
      public static String creatChongExcel(String TotalName, List<Emolument> emoluments, ApplicationHome applicationHome) throws IOException {

//          根据总表名生成存放目录
          String path=applicationHome.getDir().getParentFile().getParentFile().getAbsolutePath()+"\\salary\\submeter\\"+TotalName.substring(0,8);
          log.info(TotalName.substring(0,8));
          List<ChongQing> bankExcel=TypeUtil.getChongQingExcel(emoluments);
          String lastBankName="（重庆）";
          File file = new File(path,TotalName.substring(0,11)+lastBankName+".xlsx");
//          如果文件夹不存在就创建文件夹
          File parentFile = file.getParentFile();
          if (!parentFile.exists()){
              parentFile.mkdir();
          }
          log.info(file.getPath());
          OutputStream os=new FileOutputStream(file);
          Workbook book  = new XSSFWorkbook();
          if(file.exists()) {
              file.delete();
          }
//         if(!file.exists()) {
//             file.createNewFile();
//         }
//         此处生成表头根据银行不同需要自定义生成不同的表头
          Sheet sheet = book.createSheet("重庆");
          String[] title = {"金额","账户名", "账号", "摘要"};
          Row titleRow = sheet.createRow(0);

          for(int i = 0; i < title.length; i++) {
//             获取第一列
              Cell cell = titleRow.createCell(i );
//             写入第一列的内容
              cell.setCellValue(title[i]);
          }
          int lastRowNum = sheet.getLastRowNum();
          for(ChongQing chongQing : bankExcel) {
              Row currentRow = sheet.createRow(lastRowNum + 1);
              currentRow.createCell(0).setCellValue(chongQing.getAmount());
              currentRow.createCell(1).setCellValue(chongQing.getName());
              currentRow.createCell(2).setCellValue(chongQing.getAccount());
              currentRow.createCell(3).setCellValue(chongQing.getAbstracts());
          }
          book.write(os);
          book.close();
//         返回了一个null，后续返回存储的地址以便后端获取传递给前端
          return null;
      }


    /**
     * 方法描述：生成浦发银行Excel
     *
     * @return TotalName 总表名称
     * @author LiuZhuang
     * @date 2022/03/10
     *
     */
    public static String creatPoExcel(String TotalName, List<Emolument> emoluments, ApplicationHome applicationHome) throws IOException {
//         测试使用路径，正式使用时候应该根据总表名称生成然后传入
        String path=applicationHome.getDir().getParentFile().getParentFile().getAbsolutePath()+"\\salary\\submeter\\"+TotalName.substring(0,8);
        log.info(TotalName.substring(0,8));
        List<PuFa> bankExcel=TypeUtil.getPuFaExcel(emoluments);
        String lastBankName="（浦发）";
        File file = new File(path,TotalName.substring(0,11)+lastBankName+".xlsx");
//          如果文件夹不存在就创建文件夹
        File parentFile = file.getParentFile();
        if (!parentFile.exists()){
            parentFile.mkdir();
        }
        log.info(file.getPath());
        OutputStream os=new FileOutputStream(file);
        Workbook book  = new XSSFWorkbook();
        if(file.exists()) {
            file.delete();
        }
//         if(!file.exists()) {
//             file.createNewFile();
//         }
//         此处生成表头根据银行不同需要自定义生成不同的表头
        Sheet sheet = book.createSheet("浦发");
        int lastRowNum = sheet.getLastRowNum();
        for(PuFa puFa : bankExcel) {
            Row currentRow = sheet.createRow(lastRowNum);
            currentRow.createCell(0).setCellValue(puFa.getAccount());
            currentRow.createCell(1).setCellValue(puFa.getAmount());
            currentRow.createCell(2).setCellValue(puFa.getName());
            currentRow.createCell(3).setCellValue(lastRowNum++);
        }
        book.write(os);
        book.close();
//         返回了一个null，后续返回存储的地址以便后端获取传递给前端
        return null;
    }
}
